Understanding Grids and Scroll Areas and How to Access and Traverse the Data within Them 

Traversing Through Scroll Levels Using PeopleCode Page 1 

 © Copyright 2012

–

Copyright © 2012 Printed in the United States of America.  All rights reserved. Except as permitted under the United States Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of the  publisher. The views expressed in this book are solely those of the author and do not represent the views of any other party or parties.

PeopleSoft, PeopleTools, PeopleCode, PeopleBooks and nVision are registered trademarks of PeopleSoft Inc. This book is an independent creation of the author and has been published without license, authorization or participation of ORACLE. Information contained in this work had been obtained from sources believed to be reliable. However, neither DMT SOLUTIONS, INC. nor the author guarantee the accuracy or completeness of any information published herein and shall not be responsible for any errors, omissions, or damage arising from the use of this publication. This work is published with the understanding that DMT Solutions, Inc. and its authors are supplying information but are not attempting to render engineering or other professional services. If such services are required, the assistance of an appropriate professional should be sought. You are permitted to print one copy of this eBook for yourself. is illegal to distribute or copy this book in any part.

Traversing Through Scroll Levels Using PeopleCode Page 2 

 © Copyright 2012

–

It

Table Of Contents

Introduction ........................................... 4  Understanding Scroll Levels and Grids .................. 5  How The Component Buffer Loads Data into Scrolls ....... 7  Accessing Scroll and Grid Data with PeopleCode ........ 10  Bonus Section......................................... 19 

Traversing Through Scroll Levels Using PeopleCode Page 3 

 © Copyright 2012

–

Introduction This short yet informative guide has been created to assist you with the ability to learn how to access various levels of a scroll or grid on a PeopleSoft component.

I’m sure you have had many

instances where you needed to modify an existing page or component or even create a new one that involved grids and multiple scroll levels. It is very important to understand how to navigate through the data in scroll areas, especially when there are multiple scroll areas and grids on a page.

This guide is broken down into a few different areas.

First, we

will discuss what grids and scroll levels are and how PeopleSoft views them and accesses them.

Secondly, we will walk through the

entire process of how you access each scroll level using PeopleCode, step by step.

This will provide you with the specific code that you can use over and over for any new component or page that you either build or modify. So, with all of that said let’s get started with

understanding what scroll levels and grids are.

Traversing Through Scroll Levels Using PeopleCode Page 4 

 © Copyright 2012

–

Unders Und ers tanding S croll Level Levels and G rids I would like to first, talk briefly about the various scroll levels of a component.

A scroll level is similar to the scroll areas or

grids on a page and the level where they reside.

For the purpose of

this guide, I am assuming that you understand what a component and page is and how it is created with the various records and fields in Application Designer.

Think of a Scroll area and grid as a type of hierarchical container of multiple fields that are organized. Mostly, all of the fields within a scroll or a grid belong to one table. There may be other fields from other records.

However, they are usually there as

display only and are somehow related or are work records. But for now, let’s just assume we’re talking about one record per scroll or

grid.

For scroll areas, the fields are not necessarily displayed in any specific uniform fashion. In other words, they can be placed on the page as individual fields in a free flowing manner. are displayed in a uniform fashion.

Grids, however,

In fact, they are similar to a

spreadsheet where the fields are displayed in columns.

Scroll levels in the page definition allow PeopleSoft to maintain common key information for child record definitions.

You can

usually identify a scroll area by the plus and minus icons in the upper right corner of the object.

When you see these, you are usually in a scroll area.

The plus and

minus control buttons allow you to insert and delete rows within the scroll area. Traversing Through Scroll Levels Using PeopleCode Page 5 

 © Copyright 2012

–

Every component has at least one level 0 record definition. There can only be one row of data for each level 0 record definition because scroll areas begin at scroll level 1.

However, what we want to focus on are the PeopleSoft pages and components that include multiple scroll areas and grids. There can be multiple scroll areas on a page at the same level. And there can also be multiple scroll areas one within another. When you have scroll areas within a scroll area, you are dealing with a parent child relationship.

It seems confusing at times, but the higher

scroll area on the page is really the lower level, starting with level 1.

Then, if there is another scroll area within that scroll,

it is known to be at level 2, and so on.

The fields controlled by one scroll area or grid are associated with only one record. scroll levels.

Here are some basic rules for scroll areas or The keys of a scroll level 1 record definition must

include the same search keys as level 0, with at least one additional key.

This is simply the same rule or definition of a

child record.

PeopleSoft allows only one primary record definition per scroll or grid.

The purpose of a scroll is to control input into a single SQL

table. There can be more than one scroll on level 1. However, each scroll has its own primary record definition. The rows of data within one scroll are completely independent from the rows of data within another scroll at level 1.

As we mentioned earlier, there can be additional scroll levels added to a component on multiple levels.

However, the maximum possible

value of a scroll level for a component is 3. Each Scroll Level is subordinate to the one above it. The Scroll Levels are managed by common keys between the tables using a parent/child relationship. In other words, every page has a level 0. In addition, a page can have Traversing Through Scroll Levels Using PeopleCode Page 6 

 © Copyright 2012

–

a level 1, level 2, and level 3.

However, that is as deep as it

goes.

These are known as:

  Level 0



  Level 1   Level 2





  Level 3



This means there could be a level 1 on the page. The level 1 record is subordinate to level 0, meaning that it is a child record of the level 0 record.

Furthermore, there could be 1 or more level 1

record on the component.

How The Com C ompone ponent nt Buffe B ufferr Load Loadss Data Data into S crol cr olls ls When a page is loaded to the browser or into the PeopleSoft component buffer, the component processor will work its way down through each level.

Once it loads the data from the first row of

level one, it looks to see if there is a level 2. If there is, it will load the first row of the level 2 record.

If there is not a

level 2, it will then load the remaining field rows at level one.

Once it loads the first row of level 2, it looks to see if there is a level 3 row that is subordinate to itself. load the rows.

If there is it will

So basically, if the component processor retrieves

all of the data that is subordinate to a specific-level, or when it can’t find any more child record definitions, it will retrieve the

next row of data at the current level.

Then, it will continue to

work its way down through the all of the levels on the page.

Picture a page that has 3 scroll areas associated with it. a level 0, level 1, level 2 and a level 3. Traversing Through Scroll Levels Using PeopleCode Page 7 

There is

 © Copyright 2012

–

In the following diagram, I have illustrated how the component processor allocated the data that is stored in several different tables.

In this example there are 4 rows in the child table, 3 rows

in the grand-child table, and 1 row in the great grand-child record.

Notice, that I also labeled each row with a letter.

I did this so

that I can list out the exact order that the component processor will allocate and load the buffer. A,B,F,I,G,C,D,H,E.

The order is as follows:

If you are interested in understanding the

component buffer and the component processor and how it works, including knowing all of the PeopleCode events and the order in which they are triggered, I suggest you download the guide “Understanding the Component Processor and Component Buffer. ” 

This basically sums up how scroll areas, levels, and grids work within a PeopleSoft page or component.

Now, let’s dive into how to

access the data and navigate through each record and row at each level on the page.

This will involve object PeopleCode and the use

of Rowsets.

Traversing Through Scroll Levels Using PeopleCode Page 8 

 © Copyright 2012

–

I will break the next section down by the uses of rowsets and navigating through scroll areas.

For example, I will give you the

breakdown of code when you need to:

  Access rows of data at level 0



  Access rows of data that are not the main record



  Fill rows of data into a scroll area   Access rows of data at multiple levels





  Manipulate the data within scroll levels or grids



Traversing Through Scroll Levels Using PeopleCode Page 9 

 © Copyright 2012

–

 A c c es s i ng S c r oll and G ri d Data with wi th P eopleCode eopleC ode Before we begin the specific examples I mentioned above, I would like to first explain the definition and uses of a RowSet.

A RowSet is a PeopleSoft object that can be instantiated into the PeopleSoft component buffer.

In addition, it is nothing more than a

collection of rows associated with the data that is in the buffer. For example, the data within a specific scroll is a RowSet.

In

addition, the data at level 0 can also be a RowSet, because by its definition, it’s just a collection of Rows.

In the case of the

level 0 scroll, it might be one row, or could be multiple rows if it is an effective dated row.

When you are working with RowSets you basically have two types. First, you have the type of RowSet that is populated with data from the Component Buffer (or data on the page).

Secondly, you have the

type of RowSet that can be a stand-alone RowSet. similar to an array.

collection of rows.

This type is

It’s basically an empty structure of a

Once you instantiate a stand-alone RowSet, you

can populate it with data using specific methods that I‘ll show you

in the upcoming chapters. Instantiate a RowSet The very first piece of PeopleCode that we must have is in relation

to the RowSet.

We must first Instantiate a RowSet object before we

can do anything at all with the data in the scroll levels and grids. When we speak of objects in PeopleCode we are really talking about classes. RowSet is its own class that has its own functions, methods, and properties.

So once you instantiate the RowSet object

you can apply the functions, methods and properties to it. In other words, once you create a space in the component buffer for the Traversing Through Scroll Levels Using PeopleCode Page 10 

 © Copyright 2012

–

RowSet you can play with it.

For example, you can load data into

it, access specific rows, get the specific fields, and more importantly, get to the next level or scroll area and fetch the data there.

Declaring RowSet Variables We must first declare our object to be what it is; a RowSet.

When

you program using PeopleCode, you should always declare your objects as Local so as to not cause problems when users navigate from component to component. So with that said, let’s declare a RowSet

object.

Local Rowset &MyRowSet_0; Local Rowset &MyRowSet_1; Local Rowset &MyRowSet_2; Local Rowset &MyRowSet_3;

I have basically declared two variables named MyRowSet_0 and MyRowSet_1 as an object type of Rowset, and so on.

Now, my

intention is to use the first variable for accessing the Level 0 scroll and the second for accessing the Level 1 scroll.

You should

probably use a more descriptive variable name in your actual program.

I recommend using the actual record that is associated

with the scroll or grid.

Instantiate Your RowSet Object

There are two different ways to Instantiate a RowSet. this earlier.

I mentioned

The first way is to Instantiate it from data that is

in the component buffer.

You do this by using the function

GetLevel0. This grabs the data at Level 0 and stores it in the

MyRowSet_0 RowSet object.

So, if I was dealing with the JOB_DATA

page and I issued this code, I would have data from the PER_ORG_ASGN Record.

Believe it or not, the JOB record is actually at level 1.

Traversing Through Scroll Levels Using PeopleCode Page 11 

 © Copyright 2012

–

You can always open a component in Application Designer and click on the Structure tab to see the various levels.

Here is the code to instantiate the data from Level 0 &MyRowSet_0 = GetLevel0();

The other way to Instantiate a RowSet object is to create a standalone RowSet that contains an empty structure of the Level 0 record. You do this with the following code:

&MyRowSet_0 = CreateRowset(REC CreateRowset(RECORD.PER_ORG_ASGN) ORD.PER_ORG_ASGN); ;

This created placed the structure of the PER_ORG_ASGN record into my RowSet.

Traversing Through Scroll Levels Using PeopleCode Page 12 

 © Copyright 2012

–

OK, now that we have instantiated our RowSet object, we can actually get to the Level 1 RowSets and also get to the data at Level 0. There is no other way to get to Level 1 without first getting the RowSet for Level 0.

 Access rows of data at level 0 Now that we have instantiated our object we can access any or all of the rows of data at Level 0. We do this through the GetRow method. However, since this is the default method for the RowSet we really don’t need to call the method by name; it is implied.  

Therefore, if we wanted to access the third row of the Level 0 scroll we can either issue this code: &MyRow = &MyRowSet_0(3); &MyRow = &MyRowSet_0.GetRow(3); &MyRowSet_0.GetRow(3);

Both lines of code do the same thing. Once you have the RowSet, you can invoke any of its methods. Here is a link to the PeopleBooks for RowSet Methods. Methods.

Now, you can access the fields and data within your Level 0 RowSet as such:

&MyEMPLID = &MyRowSet_0.Ge &MyRowSet_0.GetRow(5).PER_ORG_A tRow(5).PER_ORG_ASGN.EMPLID.Value; SGN.EMPLID.Value;

 Access rows of data that are not the main record

Let’s assume that your scroll area or Grid has a main record of PER_ORG_ASGN but there are also other records and fields in the grid or Scroll.

You still access them the same way as I indicated above.

However, you indicate the record and field not the main record. example, our JOB_DATA component has the PER_ORG_ASGN as the main record at Level 0 but also has several other records like DERIVED_NAME.

Traversing Through Scroll Levels Using PeopleCode Page 13 

 © Copyright 2012

–

For

Let’s assume we want to get the PERSON_NAME data from the Scroll

Level 0.

Here is the code:

Local RowSet &MyRowSet_0; &MyRowSet_0 = CreateRowset(RE CreateRowset(RECORD.PER_ORG_ASG CORD.PER_ORG_ASGN); N); &MyName = &MyRowSet_0.GetR &MyRowSet_0.GetRow(3).DERIVED_NAM ow(3).DERIVED_NAME.PERSON_NAME.Va E.PERSON_NAME.Value; lue;

Fill rows of data into a scroll area

Regarding our stand-alone RowSet, you can fill the rows with data using the following fill method:

&EMPLID = '100001'; &MyRowSet_0.Fill("where &MyRowSet_0.Fil l("where EMPLID = :1", &EMPLID);

The above code fills the RowSet with rows where the EMPLID is equal to 100001.

Once the RowSet is filled you can use other methods for

copying, comparing, or performing other manipulation of the data.

 Access rows of data at multiple levels

Now that you have instantiated the RowSet and have the Rows for Level 0, you can now traverse through the data to get to the other scroll levels.

For example, let’s say that I want to go through the

various levels of Tax Data on the JOB_DATA component.

See the

Structure and Levels in the image below.

Traversing Through Scroll Levels Using PeopleCode Page 14 

 © Copyright 2012

–

Basically, we have 4 Levels here and they are:

  Level 0 – PER_ORG_ASGN   Level 1 - FED_TAX_DATA





  Level 2 – STATE_TAX_DATA



  Level 3 – LOCAL_TAX_DATA



If I want to get to the data from the LOCAL_TAX_DATA record on the page, I will first need to have a Level 2 RowSet, which Means I need a Level 1 RowSet, which means I need a Level 0 RowSet. point?

Get the

So Let’s get started and get the LOCALITY Field Value from

the LOCAL_TAX_DATA scroll on the page.

Here are the steps and code to get there assuming we are going after the first row of each level.

I’ll show you how to loop through each

row later. Local Rowset &MyRowSet_0; Local Rowset &MyRowSet_1; Local Local Rowset Rowset &MyRowSet_2; &MyRowSet_3; Traversing Through Scroll Levels Using PeopleCode Page 15 

 © Copyright 2012

–

&MyRowSet_1 &MyRowSet_2 &MyRowSet_3 &MyLocality

= = = =

GetLevel0()(1).GetRowset(Scroll.FED_TAX_DATA); GetLevel0()(1).GetRowset(Scroll .FED_TAX_DATA); &MyRowSet_1(1).GetRowset(Scroll &MyRowSet_1(1). GetRowset(Scroll.STATE_TAX_DATA); .STATE_TAX_DATA); &MyRowSet_2(1).GetRowset(Scroll.L &MyRowSet_2(1). GetRowset(Scroll.LOCAL_TAX_DATA); OCAL_TAX_DATA); &MyRowSet_3(1).LOCAL_TAX_DATA.L &MyRowSet_3(1). LOCAL_TAX_DATA.LOCALITY.Value; OCALITY.Value;

There you have it.

Now you know how to get to each scroll area and

the data and fields in them.

Of course, this is assuming you are

going after the first row in each.

Next we’ll assume that we have a

grid or scroll area with multiple rows that we need to loop through. Loop Through and Manipulate the data within scroll levels or grids

There are many occasions when there are multiple scroll levels each having multiple rows of data. row on every level.

In some cases we need to touch every

For example, let’s assume that I wanted to loop

through all of the rows at every level and update some flag or enable or disable some property.

We walked through getting the first row of each scroll level in the previous example.

Now, we’ll loop through all of the rows at each

level. Local Local Local Local

Rowset Rowset Rowset Rowset

&MyRowSet_0; &MyRowSet_1; &MyRowSet_2; &MyRowSet_3;

&MyRowSet_1 = GetLevel0()(1).G GetLevel0()(1).GetRowset(Scroll. etRowset(Scroll.FED_TAX_DATA); FED_TAX_DATA); For &a = 1 to &MyRowSet_1.ActiveRowCount &MyRowSet_1.ActiveRowCount /* Get the RowSet for the Next Level */ &MyRowSet_2 = &MyRowSet_1(&a) &MyRowSet_1(&a).GetRowset(Scrol .GetRowset(Scroll.STATE_TAX_DATA) l.STATE_TAX_DATA); ;  For &b = 1 to &MyRowSet_2.ActiveRowCount /* Get the RowSet for the Next Level */ &MyRowSet_3 = &MyRowSet_2(&b). &MyRowSet_2(&b).GetRowset(Scroll. GetRowset(Scroll.LOCAL_TAX_DATA); LOCAL_TAX_DATA); For &c = 1 to &MyRowSet_3.ActiveRowCount /* Get the value for each row at Level 3 */  */   &MyLocality = &MyRowSet_3(&c).L &MyRowSet_3(&c).LOCAL_TAX_DATA.LO OCAL_TAX_DATA.LOCALITY.Value; CALITY.Value; End-For; End-For; End-For;

Traversing Through Scroll Levels Using PeopleCode Page 16 

 © Copyright 2012

–

If you notice from the above we have a loop within a loop within a loop.

Essentially I get the first Row from level 0 and then Get my

Level one RowSet.

Then I loop through every row in Level 1.

For

each Row in level 1 I get the RowSet for Level 2 and loop through those rows and so on.

Of course, you can manipulate the data,

retrieve the data, or change and set properties of the fields and data at each level. You can also insert and delete rows of data at any scroll level by implementing the following methods.

&MyRowSet_2.DeleteRow(&b); &MyRowSet_2.InsertRow(&b);

OK so we have Scrolled through, pulled data out inserted data in, deleted and inserted data into a scroll on the page, what else might we want to do?

 Manipulating Data In A Grid

When you work with Grids, there are times when you need to wipe out the data and fill the grid with other data that you want.

This is a

common practice that a lot of developers use. Below is an example of taking data within a grid, flushing it (or deleting it) and refilling it with new data. Again, we still need to instantiate the RowSet before we can do anything at all with the data in the Grid. Local Rowset &COMPETENCIES; &MyRowSet_1 =GetLevel0()(1). =GetLevel0()(1).GetRowset(Scroll GetRowset(Scroll.FED_TAX_DATA); .FED_TAX_DATA);

Once we have instantiated the object we can use the Flush method to erase all of the data in the buffer and on the page, followed by filling the data with other specific data we want.

Traversing Through Scroll Levels Using PeopleCode Page 17 

 © Copyright 2012

–

&MyRowSet_1.Flush(); &MyRowSet_1.Select(Record.FED_TAX &MyRowSet_1.Sele ct(Record.FED_TAX_DATA, _DATA, "WHERE EMPLID = :1 AND EFFDT = %DATEIN(:2) AND COMPANY = :3 AND STATE = :4", &EMPLID, &EFFDT, &COMPANY, &STATE );

This flushes all of the data and re-fills the grid (in the component buffer) with the data returned in my select statement. This never updates the database unless you save the component.

One other important tip for working in Grids and Scroll areas is to remember that inserting and deleting rows will shift data up on the page.

So it is best to always delete from the bottom up making your

for loop work backwards from the ActiveRowCount to the first row.

Traversing Through Scroll Levels Using PeopleCode Page 18 

 © Copyright 2012

–

B on onus us S ection ection Here is a simple example of an Application Engine program that loops through a RowSet of employees and sends a batch email.

Introduction

Since the inception of PeopleTools version 8, many new features and tools related enhancements have been introduced. One of the best enhancements we have found is inside the Application Engine. PeopleSoft has made the application engine very powerful toolset with a wide range of capabilities.

Prior to version eight Many powerful application were written using COBOL and SQR. However, PeopleSoft is beginning to convert most of the COBOL and SQR programs into application engine programs. Prior to version eight Peoplesoft’s application engine was not built into the application designer toolset. It was very cumbersome and difficult to use. Since adding the application engine into the application designer, there are many more options and capabilities. For example, you can now use PeopleCode objects and SQL objects within your application engine program.

Designing Application Engine programs has become easier than ever, and is a definite candidate to replace SQR programs. Our favorite feature is the ability to include PeopleCode within your App engine programs. With that said, I want to show you a quick and easy way to send emails from within an Application Engine program.

Example of Sending Batch Emails from Application Engine Traversing Through Scroll Levels Using PeopleCode Page 19 

 © Copyright 2012

–

If you want to efficiently write useful application engine code, then you will find this code example, extremely useful. Did you know you could easily send email messages from within your app engine programs?

Well you can with just a few lines of code. You may find it useful to send an email message to your PS administrator when a certain process is completed. Or perhaps you want to send an email to all employees indicating that their paychecks are available to be viewed via the Employee Self Service ePay application.

Either way, you can easily create a simple application engine program that will send your email message with these few lines of code that I am about to show you. And, once you complete your application, you can easily add the AE program to a PS job stream so that it will execute after your Pay Confirm process.

You will first need to create a New Application Engine program. Here is how to do this..

From the Application Designer choose 1) File

New and select Application Engine.

2) You will see new definition created for you containing the MAIN section with a step entitled STEP01. 3) Simply right click your mouse on the step (STEP01) and choose Insert Action. 4) A new action will be inserted and will default to SQL. 5) Change this action to PeopleCode. 6) Save your program Traversing Through Scroll Levels Using PeopleCode Page 20 

 © Copyright 2012

–

7) Then double click on the PeopleCode action. Now you are ready to insert the code below. I will fully explain this code.

REM **********************************************; REM ** Instantiate the record and SQL objects to obtain data *; REM **********************************************; &MY_DATA_REC = CreateRecord(Record.EMPLOYEES); &MY_DATA_SQL = CreateSQL("%selectall(:1) WHERE PAYGROUP = :2"); &MY_DATA_SQL.Execute(&MY_DATA_REC, "OUR_PAYGROUP");

REM REM REM REM

**********************************************; ** Loop through all of the data one row at a time for *; ** all rows *; **********************************************;

While &MY_DATA_SQL.Fetch(&MY_DATA_REC); REM **********************************************; REM ** Set all necessary email parameters REM **********************************************; &MAIL_FLAGS = 0; &MAIL_TO = &MY_DATA_REC.EMAILID.Value; &MAIL_CC = ""; &MAIL_BCC = "";

&MAIL_SUBJECT = "Pay check is Available Online for Viewing"; &NAME = &MY_DATA_REC.NAME.Value; &MAIL_TEXT_BODY = "Dear " | &NAME | ", Your paycheck is now available for viewing online."; &MAIL_FILES = ""; &MAIL_TITLES = "";

Traversing Through Scroll Levels Using PeopleCode Page 21 

 © Copyright 2012

–

REM **********************************************; REM ** Send the email message for the current employee; REM **********************************************; &RET_CODE = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT_BODY, &MAIL_FILES, &MAIL_TITLES); REM ********************************************************; REM ** Check the status code to ensure the mail was sent successfully; REM ********************************************************;

If Not (&RET_CODE = 0) Then WinMessage("Return status from mail = " | &RET_CODE); End-If; End-While; REM ** Don�t forget to close the SQL stream **; &MY_DATA_SQL.Close();

As you can see from the above code, it is extremely simple to loop through data with your set criteria and send email messages from within your application Engine program.

Basically, I have used PeopleCode to create a record MY_DATA_REC from the EMPLOYEES table. After this, I created a SQL statement that basically selects from the record PS_EMPLOYEES where the PAYGROUP is some value.

Next, I use a while loop to fetch each row from the record, one by one. As I grab the employee�s data I build my email message and use the SendMail PeopleCode function. Traversing Through Scroll Levels Using PeopleCode Page 22 

 © Copyright 2012

–

Notice the Parameters that get passed to the SendMail command. You can send file attachments by simply giving a file path/name to the &MAIL_FILES variable.

This is a very basic example of how to send emails. Once, you determine what you need to do, you can always, add more functionality and get more creative with your code.

Traversing Through Scroll Levels Using PeopleCode Page 23 

 © Copyright 2012

–